ALTER TABLE cmsCustomer
ADD Identification nvarchar(128)
GO
ALTER TABLE cmsCustomer
ADD CustomerCode nvarchar(128)
GO
ALTER TABLE cmsCustomer
ADD CreatedDate datetime null default(getdate())
GO
ALTER TABLE cmsCustomer
ADD LastSaveDate datetime null
GO
ALTER TABLE cmsCustomer
ADD CreatedById int
GO
ALTER TABLE cmsCustomer
ADD LastModifiedById int

GO
ALTER PROCEDURE [dbo].[spcmsCustomer_GetAll] 
AS
Select 
[Id],
[FullName],
[UserName],
[Password],
[Sex],
[Brithday],
[Email],
[Phone],
[Address],
[RegisterDay],
[IsEnable],
[IsVangLai],
[Identification],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedById]

from 
cmsCustomer
Return
GO

ALTER PROCEDURE [dbo].[spcmsCustomer_GetByPK] 
	(
@Id int
)
AS
SELECT	 
[Id],
[FullName],
[UserName],
[Password],
[Sex],
[Brithday],
[Email],
[Phone],
[Address],
[RegisterDay],
[IsEnable],
[IsVangLai],
[Identification],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedById]

FROM 
cmsCustomer  
WHERE 
[Id]=@Id


RETURN
GO

ALTER PROCEDURE [dbo].[spcmsCustomer_DeleteAll] 
AS Delete  
from 
cmsCustomer
Return
GO

ALTER PROCEDURE [dbo].[spcmsCustomer_DeleteByPK] 
	(
@Id int
)
AS
Delete  from 
cmsCustomer 
where 
[Id]=@Id


Return
GO

ALTER PROCEDURE [dbo].[spcmsCustomer_Insert] 
	(
@FullName nvarchar(50)=null,
@UserName nvarchar(50)=null,
@Password nvarchar(50)=null,
@Sex bit=null,
@Brithday datetime=null,
@Email nvarchar(50)=null,
@Phone nvarchar(50)=null,
@Address nvarchar(250)=null,
@RegisterDay datetime=null,
@IsEnable bit=null,
@IsVangLai bit=null,
@Identification nvarchar(128)=null,
@CustomerCode nvarchar(128)=null,
@CreatedDate datetime=null,
@LastSaveDate datetime=null,
@CreatedById int=null,
@LastModifiedById int=null
)
AS
Insert Into 
cmsCustomer 
(
[FullName],
[UserName],
[Password],
[Sex],
[Brithday],
[Email],
[Phone],
[Address],
[RegisterDay],
[IsEnable],
[IsVangLai],
[Identification],
[CustomerCode],
[CreatedDate],
[LastSaveDate],
[CreatedById],
[LastModifiedById]
) 
values(
@FullName,
@UserName,
@Password,
@Sex,
@Brithday,
@Email,
@Phone,
@Address,
@RegisterDay,
@IsEnable,
@IsVangLai,
@Identification,
(SELECT dbo.GenerateCustomerCode()),
GETDATE(),
 CASE WHEN @LastSaveDate = '1900-1-1' THEN NULL ELSE @LastSaveDate END,
@CreatedById,
@LastModifiedById
)
RETURN Scope_identity()
GO

ALTER PROCEDURE [dbo].[spcmsCustomer_UpdateByPK] 
	(
@Id int,
@FullName nvarchar(50)=null,
@UserName nvarchar(50)=null,
@Password nvarchar(50)=null,
@Sex bit=null,
@Brithday datetime=null,
@Email nvarchar(50)=null,
@Phone nvarchar(50)=null,
@Address nvarchar(250)=null,
@RegisterDay datetime=null,
@IsEnable bit=null,
@IsVangLai bit=null,
@Identification nvarchar(128)=null,
@CustomerCode nvarchar(128)=null,
@CreatedDate datetime=null,
@LastSaveDate datetime=null,
@CreatedById int=null,
@LastModifiedById int=null
)
AS
Update  cmsCustomer Set 
[FullName]=@FullName,
[UserName]=@UserName,
[Password]=@Password,
[Sex]=@Sex,
[Brithday]=@Brithday,
[Email]=@Email,
[Phone]=@Phone,
[Address]=@Address,
[RegisterDay]=@RegisterDay,
[IsEnable]=@IsEnable,
[IsVangLai]=@IsVangLai,
[Identification]=@Identification,
[CustomerCode]=@CustomerCode,
[CreatedDate]=@CreatedDate,
[LastSaveDate]=CASE WHEN @LastSaveDate = '1900-1-1' THEN NULL ELSE @LastSaveDate END,
[CreatedById]=@CreatedById,
[LastModifiedById]=@LastModifiedById
 
where 
[Id]=@Id


Return
GO
CREATE FUNCTION GenerateCustomerCode()
RETURNS NVARCHAR(128)
AS
BEGIN
	DECLARE @customerCode nvarchar(128);
	DECLARE @oldCode int;
	SET @customerCode = (SELECT top 1 CustomerCode FROM cmsCustomer order by CustomerCode DESC);

	IF(@customerCode is null)
		SET @customerCode = 'VNL000001';
	ELSE
	BEGIN
		SET @oldCode = CONVERT(int,(SELECT RIGHT(@customerCode,6)));
		SET @oldCode = @oldCode + 1;
		if(@oldCode<10) -- 0-> 9
			SET @customerCode = 'VNL' + '00000' + CONVERT(nvarchar,@oldCode);
		ELSE IF (@oldCode >=10 AND @oldCode < 100)
			SET @customerCode = 'VNL' + '0000' + CONVERT(nvarchar,@oldCode);
		ELSE IF (@oldCode >=100 AND @oldCode < 1000)
			SET @customerCode = 'VNL' + '000' + CONVERT(nvarchar,@oldCode);
		ELSE IF (@oldCode >=1000 AND @oldCode < 10000)
			SET @customerCode = 'VNL' + '00' + CONVERT(nvarchar,@oldCode);
		ELSE IF (@oldCode >=10000 AND @oldCode < 100000)
			SET @customerCode = 'VNL' + '0' + CONVERT(nvarchar,@oldCode);
		ELSE
			SET @customerCode = 'VNL' + '' + CONVERT(nvarchar,@oldCode);
	END
	RETURN @customerCode;
END
